<?
// Database.php
// written by: David Fudge [ rkstar@mac.com ]
// created on: November 1, 2008
// last modified: November 1, 2008
//
// description:
// this file contains all the functionality required to connect to and execute queries
// on MySQL, MSSQL, and MSAccess databases.

// load dependencies here
ed::load("Core.Core");
ed::load("Core.Error");
ed::load("Data.SQLQuery");
ed::load("Data.SQLResult");

class Database extends Core
{
	private $dbhost;		// database host name
	private $dbuser;		// database user name
	private $dbpass;		// database password
	private $dbname;		// database name
	private $dbtype;		// database type
	private $dsn;			// dsn for oracle or odbc databases
	private $link;			// resource link identifier
	private $query;			// the current query

	// constructor
	public function __construct( $dbhost=DB_HOST, $dbuser=DB_USER, $dbpass=DB_PASS,
								 $dbname=DB_NAME, $dbtype=DB_TYPE, $dsn=DB_DSN )
	{
		parent::__construct();
		// set vars
		$this->host     = $dbhost;
		$this->username = $dbuser;
		$this->password = $dbpass;
		$this->database = $dbname;
		$this->dbtype   = $dbtype;
		$this->dsn      = $dsn;
		// connect to the db
		return $this->connect();
	}
	
	// connect to the database
	public function connect()
	{
		// connect based on the type of database we're using
		switch( strtolower($this->dbtype ) )
		{
			case "mysql":
				if( !($this->link = mysql_connect($this->host, $this->username, $this->password)) )
				{
					// check for debugging
					if( $this->debug() ) { $this->error->add("Could not connect to db b/c: ".mysql_error()); }
					return false;
				}
				else { mysql_select_db($this->database, $this->link); }	// select the db
			break;
			
			case "mssql":
				if( !($this->link = mssql_connect($this->host, $this->username, $this->password)) )
				{
					// check for debugging
					if( $this->debug() ) { $this->error->add("Could not connect to db b/c: ".mssql_get_last_message()); }
					return false;
				}
				else { mssql_select_db($this->database, $this->link); }	// select the db
			break;
			
			case "odbc":
				if( !($this->link = odbc_connect($this->dsn, $this->username, $this->password)) )
				{
					// check for debugging
					if( $this->debug() ) { $this->error->add("Could not connect to db b/c: ".odbc_errormsg()); }
					return false;
				}
			break;
		}

		return true;
	}
	
	// reconnect to the db
	public function reconnect()
	{
		// connect based on the type of database we're using
		switch( strtolower($this->dbtype) )
		{
			case "mysql":
				if( !$this->link ) { return $this->connect(); }
				if( !mysql_ping($this->link) )
				{
					// check for debugging
					if( $this->debug() ) { $this->error->add("Could not reconnect to db b/c: ".mysql_error()); }
					return false;
				}
				else { mysql_select_db($this->database, $this->link); }	// select the db
			break;
			
			case "mssql":
			break;
			
			case "odbc":
			break;
		}
	}
	
	// disconnect from database
	public function disconnect()
	{
		switch( strtolower($this->dbtype) )
		{
			case "mysql":	mysql_close($this->link);	break;
			case "mssql":	mssql_close($this->link);	break;
			case "odbc":	odbc_close($this->link);	break;
		}
	}
	
	// get the available fields in a table
	public function getTableFieldList( $table )
	{
		$fieldList = array();
		// a simple query to get the field list...
		$q = "show fields from ".$table;
		if( !($r = $this->execute($q)) ) { return $fieldList; }
		// we have fields... continue
		while( $r->next() ) { array_push($fieldList,$r->Field); }
		
		return $fieldList;
	}
	
	// execute the query
	public function execute( $sqlQuery )
	{
		// sanity
		if( strlen($sqlQuery) < 1 ) { return false; }
		// more sanity
		if( !$this->link ) { $this->connect(); }
		// debugging?
		if( $this->debug() ) { print "<!-- SQL: ".$sqlQuery." -->"; }
		// set the current query
		$this->query = $sqlQuery;
		
		// run the query
		switch( strtolower($this->dbtype) )
		{
			case "mysql":
				if( !($result = mysql_query($sqlQuery, $this->link)) )
				{
					$this->error->add("MySQL Error: ".mysql_error());
					return false;
				}
			break;
			
			case "mssql":
				if( !($result = mssql_query($sqlQuery, $this->link)) )
				{
					$this->error->add("MSSQL Error: ".mssql_get_last_message());
					return false;
				}
			break;
			
			case "odbc":
				if( !($result = odbc_exec($this->link, $sqlQuery)) )
				{
					$this->error->add("ODBC Error: ".odbc_errormsg());
					return false;
				}
			break;
		}

		// instantiate an SQLResult object
		$sqlResult = new SQLResult( $result, $this->query, $this->link, $this->dbtype );
		// some housekeeping to make sure we send back a FALSE if the query failed...
		// this only applies to SELECT and INSERT queries. DELETE and INSERT queries
		// would have failed with a FALSE response before this point.
		switch( strtolower($sqlResult->getQueryType()) )
		{
			case "select":	if( $sqlResult->numrows() < 1 ) { return false; }									break;
			case "insert":	if( ($sqlResult->insertId() < 1) && ($sqlResult->numrows() < 1) ) { return false; }	break;
		}
		// return the result object
		return $sqlResult;
	}
	
	// select data from a database
	public function select( $qobject )
	{
		// sanity
		if( !is_object($qobject) ) { return false; }
		
		// start the query
		$q  = "select ";
		$q .= $qobject->getSelectFields()." from ".$qobject->getDBTable();
		$q .= (strlen($qobject->getCondition()) > 0) ? " where ".$qobject->getCondition() : "";
		$q .= (strlen($qobject->getGroup()) > 0) ? " group by ".$qobject->getGroup() : "";
		$q .= (strlen($qobject->getOrder()) > 0) ? " order by ".$qobject->getOrder() : "";
		// condition by which we'll add the asd/desc delimiter
		if( (strlen($qobject->getGroup()) > 0) || (strlen($qobject->getOrder()) > 0) )
		{
			$q .= ($qobject->getDirection()=="DESC") ? " DESC" : " ASC";
		}
		// limit conditions
		if( $qobject->getLimit() )
		{
			switch( strtolower($this->dbtype) )
			{
				case "mysql":	$q .= " limit ".$qobject->getLimit();										break;
				case "mssql":
				case "odbc":	$q .= str_replace("select ","select top ".$qobject->getLimit()." ", $q);	break;
			}
		}

		// set and execute the query
		$qobject->setQuery($q);
		return $this->execute($q);
	}

	// insert a record into our database
	public function insert( $qobject )
	{
		// sanity
		if( !is_object($qobject) ) { return false; }
		
		// get the available fields
		$fieldList = $this->getTableFieldList($qobject->getDBTable());
		
		// start the query
		$q  = "insert into ".$qobject->getDBTable()." ";
		// loop thru the fields and prepare them for the insert query
		$fields = $qobject->getValues();
		foreach( $fields->children() as $node )
		{
			// sanity :: make sure the field we're updating is available in the table
			if( !in_array($node->getName(), $fieldList) ) { continue; }
			// continue to build the query
			$field .= $node->getName().",";
			$value .= ((substr($node,0,1)=="/") && (substr($node,-1)=="/")) ? substr($node,1,-1) : "\"".$node."\"";
			$value .= ",";
		}
		$field = substr($field,0,-1);
		$value = substr($value,0,-1);
		// continue putting together the query
		$q .= "(".$field.") values (".$value.")";

		// set and execute the query
		$qobject->setQuery($q);
		if( !($r = $this->execute($q)) ) { return false; }
		// return the insert id
		return ($r->insertId() === 0) ? true : $r->insertId();
	}
	
	// replace database record/s
	public function replace( $qobject )
	{
		// sanity
		if( !is_object($qobject) ) { return false; }
		
		// get the available fields
		$fieldList = $this->getTableFieldList($qobject->getDBTable());
		
		// start the query
		$q  = "replace into ".$qobject->getDBTable()." set ";
		// loop thru the fields and prepare them for the insert query
		$fields = $qobject->getValues();
		foreach( $fields->children() as $node )
		{
			// sanity :: make sure the field we're updating is available in the table
			if( !in_array($node->getName(), $fieldList) ) { continue; }
			// continue to build the query
			$q .= $node->getName()." = ";
			$q .= ((substr($node,0,1)=="/") && (substr($node,-1)=="/")) ? substr($node,1,-1) : "\"".$node."\"";
			$q .= ",";
		}
		$q = substr($q,0,-1);
		
		// set and execute the query
		$qobject->setQuery($q);
		if( !($r = $this->execute($q)) ) { return false; }
		// return the insert id
		return ($r->insertId() === 0) ? true : $r->insertId();
	}
	
	// update database record/s
	public function update( $qobject )
	{
		// sanity
		if( !is_object($qobject) ) { return false; }

		// get the available fields
		$fieldList = $this->getTableFieldList($qobject->getDBTable());
		
		// start the query
		$q  = "update ".$qobject->getDBTable()." set ";
		// loop thru the fields and prepare them for the update query
		$fields = $qobject->getValues();
		foreach( $fields->children() as $node )
		{
			// sanity :: make sure the field we're updating is available in the table
			if( !in_array($node->getName(), $fieldList) ) { continue; }
			// continue to build the query
			$q .= $node->getName()." = ";
			$q .= ((substr($node,0,1)=="/") && (substr($node,-1)=="/")) ? substr($node,1,-1) : "\"".$node."\"";
			$q .= ",";
		}
		$q  = substr($q,0,-1);
		$q .= " where ".$qobject->getCondition();
		// set a limit on our query
		if( $qobject->getLimit() > 0 ) { $q .= " limit ".$qobject->getLimit(); }
		
		// set and execute the query which will allow us to automatically insert
		// if the update does not work...
		$qobject->setQuery($q);
		$r = $this->execute($q);
		return ($r) ? $r : $this->insert($qobject);
	}
	
	// delete a record from the database
	public function delete( $qobject )
	{
		// sanity
		if( !is_object($qobject) ) { return false; }
		
		// set up the query
		$q  = "delete from ".$qobject->getDBTable()." ";
		$q .= "where ".$qobject->getCondition();
		// set the limit
		if( $qobject->getLimit() > 0 ) { $q .= " limit ".$qobject->getLimit(); }
		
		// set and execute the query
		$qobject->setQuery($q);
		return ($this->execute($q)) ? true : false;
	}
	
	// create a table in our database
	public function createTable( $tablename, $fields, $isTemp=false )
	{
		// sanity
		if( (strlen($tablename) < 1) || !is_array($fields) || (count($fields) < 1) ) { return false; }
		
		// create the query
		$q  = "create ";
		$q .= ($isTemp) ? "temporary " : "";
		$q .= "table ".$tablename." (";
		// loop fields array and add each definition
		while( list($name,$definition) = each($fields) ) { $q .= $name." ".$definition.", "; }
		// get rid of trailing comma
		$q  = substr($q,0,-1);
		// add the closing parenthesis
		$q .= ")";
		
		// execute the query
		return ($this->execute($q)) ? true : false;
	}
	
	// get data out of our class
	public function getQuery() { return $this->query; }
	public function getLink() { return $this->link; }
	public function getType() { return $this->dbtype; }
}
?>